﻿using System;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace NorthwindProductsRetriever
{
    class NorthwindProductsRetriever
    {
        static void Main()
        {
            const string ConnectionString = @"Server=.;Database=Northwind;Integrated Security=true";
            SqlConnection dbCon = new SqlConnection(ConnectionString);
            dbCon.Open();
            Dictionary<string, List<string>> categories = new Dictionary<string, List<string>>();
            using (dbCon)
            {
                string commandText = "SELECT c.CategoryName as Category, p.ProductName as Product " +
                    "FROM Categories c JOIN products p ON p.CategoryID = c.CategoryID " +
                    "ORDER BY c.CategoryID";
                SqlCommand command = new SqlCommand(commandText,dbCon);
                SqlDataReader reader = command.ExecuteReader();
                using (reader)
                {
                    while (reader.Read())
                    {
                        string category = (string)reader["Category"];
                        string product = (string)reader["Product"];
                        if (categories.ContainsKey(category))
                        {
                            categories[category].Add(product);
                        }
                        else
                        {
                            categories.Add(category, new List<string>{product});
                        }
                    }
                }
            }

            foreach (var item in categories)
            {
                Console.WriteLine("{0}:", item.Key);
                foreach (string product in item.Value)
                {
                    Console.WriteLine("\t{0}", product);
                }
                Console.WriteLine();
            }
        }
    }
}
